﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using DotNetNuke.Common.Utilities;
using DotNetNuke.Framework.Providers;
using DotNetNuke.Entities.Tabs;
using DotNetNuke.Entities.Modules;

using iFinity.DNN.Modules.BlogFriendlyUrlProvider.Entities;

namespace iFinity.DNN.Modules.BlogFriendlyUrlProvider.Data
{
    internal class SqlDataProvider : DataProvider
    {
        #region Private Members

        private const string ProviderType = "data";
        private const string ModuleQualifier = "bmp_";

        private ProviderConfiguration _providerConfiguration = ProviderConfiguration.GetProviderConfiguration(ProviderType);
        private string _connectionString;
        private string _providerPath;
        private string _objectQualifier;
        private string _databaseOwner;
        #endregion
        #region Constructor
        /// <summary>
        /// Constructs new SqlDataProvider instance
        /// </summary>
        internal SqlDataProvider()
        {
            //Read the configuration specific information for this provider
            Provider objProvider = (Provider)_providerConfiguration.Providers[_providerConfiguration.DefaultProvider];

            //Read the attributes for this provider
            //Get Connection string from web.config
            _connectionString = Config.GetConnectionString();

            if (_connectionString.Length == 0)
            {
                // Use connection string specified in provider
                _connectionString = objProvider.Attributes["connectionString"];
            }

            _providerPath = objProvider.Attributes["providerPath"];

            //override the standard dotNetNuke qualifier with a iFinity one if it exists
            _objectQualifier = objProvider.Attributes["objectQualifier"];
            if ((_objectQualifier != "") && (_objectQualifier.EndsWith("_") == false))
            {
                _objectQualifier += "_";
            }
            else
                if (_objectQualifier == null) _objectQualifier = "";

            _objectQualifier += "ifty_";

            _databaseOwner = objProvider.Attributes["databaseOwner"];
            if ((_databaseOwner != "") && (_databaseOwner.EndsWith(".") == false))
            {
                _databaseOwner += ".";
            }
        }
        #endregion
        #region Properties

        /// <summary>
        /// Gets and sets the connection string
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
        }
        /// <summary>
        /// Gets and sets the Provider path
        /// </summary>
        public string ProviderPath
        {
            get { return _providerPath; }
        }
        /// <summary>
        /// Gets and sets the Object qualifier
        /// </summary>
        public string ObjectQualifier
        {
            get { return _objectQualifier; }
        }
        /// <summary>
        /// Gets and sets the database ownere
        /// </summary>
        public string DatabaseOwner
        {
            get { return _databaseOwner; }
        }

        #endregion 
        #region private members
        /// -----------------------------------------------------------------------------
        /// <summary>
        /// Gets the fully qualified name of the stored procedure
        /// </summary>
        /// <param name="name">The name of the stored procedure</param>
        /// <returns>The fully qualified name</returns>
        /// -----------------------------------------------------------------------------
        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + ModuleQualifier + name;
        }
        #endregion
        #region overriden dataprovider methods
        internal override void GetBlogEntriesForTab(int tabId,string cultureCode, int startingItemId, out Entities.BlogEntryInfoCol entries, out Entities.BlogInfoCol blogs, out Entities.CategoryInfoCol cats, out Entities.TagInfoCol tags, out bool isBlog6)
        {
            isBlog6 = false; //by default don't assume blog6
            blogs = new BlogInfoCol(); entries = new BlogEntryInfoCol(); cats = new CategoryInfoCol(); tags = new TagInfoCol();
            string sp = GetFullyQualifiedName("GetBlogEntriesForTab");
            SqlParameter[] parms = new SqlParameter[3];
            parms[0] = new SqlParameter("@TabId", tabId);
            parms[1] = new SqlParameter("@Locale", cultureCode);
            parms[2] = new SqlParameter("@startingItemId", startingItemId);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            while (rdr.Read())
            {
                BlogEntryInfo entry = new BlogEntryInfo();
                if (!Convert.IsDBNull(rdr["BlogId"]))
                    entry.BlogId = (int)rdr["BlogId"];
                if (!Convert.IsDBNull(rdr["ItemId"]))
                    entry.ItemId = (int)rdr["ItemId"];
                if (!Convert.IsDBNull(rdr["Title"]))
                    entry.Title = (string)rdr["Title"];
                else
                    entry.Title = "";
                if (!Convert.IsDBNull(rdr["AddedDate"]))
                    entry.AddedDate = (DateTime)rdr["AddedDate"];
                else
                    entry.AddedDate = DateTime.MinValue;
                //add to the list
                entries.Add(entry);
            }
            if (rdr.NextResult())
            {
                //get the blog information
                while (rdr.Read())
                {
                    BlogInfo blog = new BlogInfo();
                    if (!Convert.IsDBNull(rdr["BlogId"]))
                        blog.BlogId = (int)rdr["BlogId"];
                    if (!Convert.IsDBNull(rdr["Title"]))
                        blog.Title = (string)rdr["Title"];
                    else
                        blog.Title = "";
                    if (!Convert.IsDBNull(rdr["DisplayName"]))
                        blog.DisplayName = (string)rdr["DisplayName"];
                    else
                        blog.DisplayName = "";
                    if (!Convert.IsDBNull(rdr["Username"]))
                        blog.Username = (string)rdr["Username"];
                    else
                        blog.Username = "";
                    //add to list of blogs
                    blogs.Add(blog);
                }
            }
            if (rdr.NextResult())
            { 
                //categories
                while (rdr.Read())
                {
                    CategoryInfo cat = new CategoryInfo();
                    if (!Convert.IsDBNull(rdr["CatId"]))
                        cat.CatId = (int)rdr["CatId"];
                    if (!Convert.IsDBNull(rdr["Category"]))
                        cat.Category = (string)rdr["Category"];
                    if (!Convert.IsDBNull(rdr["Slug"]))
                        cat.Slug = (string)rdr["Slug"];
                    if (!Convert.IsDBNull(rdr["ParentId"]))
                        cat.ParentId = (int)rdr["ParentId"];

                    //add to list of categories
                    cats.Add(cat);
                }
            }
            if (rdr.NextResult())
            {
                while (rdr.Read())
                {
                    TagInfo tag = new TagInfo();
                    if (!Convert.IsDBNull(rdr["TagId"]))
                        tag.TagId = (int)rdr["TagId"];
                    if (!Convert.IsDBNull(rdr["Tag"]))
                        tag.Tag = (string)rdr["Tag"];
                    if (!Convert.IsDBNull(rdr["Slug"]))
                        tag.Slug = (string)rdr["Slug"];
                    //add to list of tags
                    tags.Add(tag);
                }
            }

            if (rdr.NextResult())
            {

                if (rdr.Read())
                {
                    if (!Convert.IsDBNull(rdr["IsBlog6"]))
                    {
                        if ((int) rdr["IsBlog6"] == 1)
                            isBlog6 = true;
                        else

                            isBlog6 = false;
                    }
                }
            }
            rdr.Close();
            rdr.Dispose();
        }
        internal override Dictionary<int, BlogInfo> GetBlogsForPortal(int portalId)
        {
            string sp = GetFullyQualifiedName("GetBlogsForPortal");
            SqlParameter[] parms = new SqlParameter[1];
            parms[0] = new SqlParameter("@portalId", portalId);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            Dictionary<int, BlogInfo> blogs = new Dictionary<int, BlogInfo>();
            while (rdr.Read())
            {
                int blogId = (int)rdr["BlogId"];
                string displayName = (string)rdr["DisplayName"];
                string title = (string)rdr["Title"];
                string username = (string)rdr["username"];
                BlogInfo blog = new BlogInfo();
                blog.BlogId = blogId;
                blog.DisplayName = displayName;
                blog.Title = title;
                blog.Username = username;
                if (blogId > -1 && blogs.ContainsKey(blogId) == false)
                    blogs.Add(blogId, blog);
            }
            rdr.Close();
            rdr.Dispose();
            return blogs;
        }
        internal override BlogEntryInfo GetBlogEntry(int itemId, string locale)
        {
            string sp = GetFullyQualifiedName("GetBlogEntry");
            SqlParameter[] parms = new SqlParameter[2];
            parms[0] = new SqlParameter("@itemId", itemId);
            parms[1] = new SqlParameter("@locale", locale);
            SqlDataReader rdr = SqlHelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, sp, parms);
            BlogEntryInfo entry =  new BlogEntryInfo();
            if (rdr.Read())
            {
                int blogId = (int)rdr["BlogId"];
                string title = (string)rdr["Title"];
                DateTime addedDate = (DateTime)rdr["AddedDate"];
                entry.ItemId = itemId;
                entry.BlogId = blogId;
                entry.Title = title;
                entry.AddedDate = addedDate;
            }
            rdr.Close();
            rdr.Dispose();
            return entry;
        }
        #endregion
    }
}
